package project.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import models.util.MySqlDataAccessHelper;

import project.model.PrProject;

public class PrProjectDAO {
	public static List<PrProject> getAllProjectManager(String idEmployee,String database) throws SQLException{
		List<PrProject> lstProjectManager = new ArrayList<PrProject>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		helper.open(database);
		PrProject prProject  = null;
		
		String sql = "SELECT p.IDProject, p.NameProject, p.Description, p.DateStart, p.DateComplete, p.IDManager, p.`Status`, e.FullName " +
    	"FROM pr_project p, employee e WHERE p.IDManager="+idEmployee+" AND p.IDManager = e.IDEmployee GROUP BY p.IDProject ";
		
		ResultSet rs = helper.executeQuery(sql);
		while(rs.next()){
			prProject = new PrProject();
			prProject.setIdProject(rs.getString("IDProject"));
			prProject.setNameProject(rs.getString("NameProject"));
			prProject.setDescription(rs.getString("Description"));
			prProject.setIdManager(rs.getString("IDManager"));
			prProject.setNameManager(rs.getString("FullName"));
			prProject.setDateComplete(rs.getDate("DateComplete"));
			prProject.setStatus(rs.getInt("Status"));
			prProject.setDateStart(rs.getDate("DateStart"));
			
//			prProject = new PrProject(rs.getString("IDProject"), rs.getString("NameProject"), rs.getString("Description"), rs.getString("IDManager"),
//        			rs.getString("FullName"), rs.getDate("DateComplete"), rs.getInt("Status"), rs.getDate("DateStart"));
			lstProjectManager.add(prProject);
		}
		
		return lstProjectManager;
	}
	
	public static PrProject getProjectManagerById(String id,String database) throws SQLException{
		PrProject prProject = null;
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		helper.open(database);
		
		String sql = "SELECT p.IDProject, p.NameProject, p.Description, p.DateStart, p.DateComplete, p.IDManager, p.`Status`, e.FullName " +
    	"FROM pr_project p, employee e WHERE p.IDManager = "+id+" and p.IDManager = e.IDEmployee GROUP BY p.IDProject ORDER BY p.NameProject ASC";
		
		ResultSet rs = helper.executeQuery(sql);
		while(rs.next()){
			prProject = new PrProject(rs.getString("IDProject"), rs.getString("NameProject"), rs.getString("Description"), rs.getString("IDManager"),
        			rs.getString("FullName"), rs.getDate("DateComplete"), rs.getInt("Status"), rs.getDate("DateStart"));
		
		}
		return prProject;
	}

}
